﻿# -*- coding: utf-8 -*-
'''
Created on 26.05.2011

@author: Andrey Gaidel
'''

STUDENTS_BY_GROUP = \
    "SELECT * FROM dummy_student WHERE group_id = %s"

SESSION_RESULTS = \
    "SELECT dummy_mark.id, dummy_mark.examination_id, dummy_mark.student_id, dummy_mark.grade \
        FROM dummy_mark \
        JOIN dummy_examination ON dummy_mark.examination_id = dummy_examination.id \
        JOIN dummy_student ON dummy_mark.student_id = dummy_student.id \
        WHERE dummy_examination.session_id = %s AND dummy_student.group_id = %s";

SCHOLARSHIP_BY_SEMESTER = \
    "SELECT dummy_scholarship.id, dummy_scholarship.session_id, dummy_scholarship.student_id, dummy_scholarship.cash_id \
        FROM dummy_scholarship \
            JOIN dummy_session ON dummy_scholarship.session_id = dummy_session.id \
        WHERE (EXTRACT(YEAR FROM dummy_session.start_date) = %s \
            AND dummy_session.start_date >= to_date('01.09.' || to_char(EXTRACT(YEAR FROM dummy_session.start_date), '9999'), 'DD.MM.YYYY') \
            OR EXTRACT(YEAR FROM dummy_session.start_date) - 1 = %s \
            AND dummy_session.start_date < to_date('01.09.' || to_char(EXTRACT(YEAR FROM dummy_session.start_date), '9999'), 'DD.MM.YYYY')) \
            AND mod(dummy_session.number, 2) = %s";

LAST_EXAMINATIONS = \
    "SELECT dummy_examination.id, dummy_examination.session_id, dummy_examination.discipline_id, dummy_examination.group_id, dummy_examination.date \
        FROM dummy_examination \
            JOIN ( \
                SELECT dummy_session.id \
    				FROM dummy_session \
    					JOIN dummy_speciality ON dummy_session.speciality_id = dummy_speciality.id \
    					JOIN dummy_studentgroup ON dummy_speciality.id = dummy_studentgroup.speciality_id \
    					JOIN dummy_student ON dummy_studentgroup.id = dummy_student.group_id \
    				WHERE dummy_session.end_date < now() \
    					AND dummy_student.id = %s \
    				ORDER BY dummy_session.start_date DESC \
    				LIMIT 1 \
    		) AS sess ON dummy_examination.session_id = sess.id \
    	WHERE dummy_examination.group_id = %s"

LAST_SCHOLARSHIP = \
    "SELECT dummy_scholarship.id, dummy_scholarship.session_id, dummy_scholarship.student_id, dummy_scholarship.cash_id \
    	FROM dummy_scholarship \
    		JOIN ( \
    			SELECT dummy_session.id \
    				FROM dummy_session \
    					JOIN dummy_speciality ON dummy_session.speciality_id = dummy_speciality.id \
    					JOIN dummy_studentgroup ON dummy_speciality.id = dummy_studentgroup.speciality_id \
    					JOIN dummy_student ON dummy_studentgroup.id = dummy_student.group_id \
    				WHERE dummy_session.end_date < now() \
    					AND dummy_student.id = %s \
    				ORDER BY dummy_session.start_date DESC \
    				LIMIT 1 \
    		) AS sess ON dummy_scholarship.session_id = sess.id \
    	WHERE dummy_scholarship.student_id = %s"

LAST_MINWAGE = \
    "SELECT dummy_minwage.id, dummy_minwage.money, dummy_minwage.\"date\" \
        FROM dummy_minwage \
        ORDER BY dummy_minwage.\"date\" DESC \
        LIMIT 1"

LAST_SSF = \
    "SELECT dummy_ssf.id, dummy_ssf.money, dummy_ssf.date \
    	FROM dummy_ssf \
    	WHERE dummy_ssf.date < now() \
    	ORDER BY dummy_ssf.date DESC \
    	LIMIT 1"

LAST_SESSION = \
    "SELECT dummy_session.id, dummy_session.speciality_id, dummy_session.number, dummy_session.start_date, dummy_session.end_date \
    	FROM dummy_session \
    		JOIN dummy_speciality ON dummy_session.speciality_id = dummy_speciality.id \
    		JOIN dummy_studentgroup ON dummy_speciality.id = dummy_studentgroup.speciality_id \
    		JOIN dummy_student ON dummy_studentgroup.id = dummy_student.group_id \
    	WHERE dummy_session.end_date < now() AND dummy_student.id = %s \
    	ORDER BY dummy_session.start_date DESC \
    	LIMIT 1"

NEXT_SESSION = \
    "SELECT sess.id, sess.speciality_id, sess.number, sess.start_date, sess.end_date \
        FROM dummy_session as sess JOIN dummy_session as last_sess ON sess.speciality_id = last_sess.speciality_id \
        WHERE sess.end_date > last_sess.end_date and last_sess.id = %s \
        ORDER BY sess.end_date \
        LIMIT 1"

LAST_REQUEST = \
    "SELECT dummy_welfarerequest.id, dummy_welfarerequest.student_id, dummy_welfarerequest.date, dummy_welfarerequest.cash, dummy_welfarerequest.description \
    	FROM dummy_welfarerequest \
    	WHERE dummy_welfarerequest.student_id = %s \
    	ORDER BY dummy_welfarerequest.date DESC \
    	LIMIT 1"
